/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package kz.dasm.baing.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import javax.sql.DataSource;
import kz.dasm.baing.db.entity.CompanyPerson;
import kz.dasm.baing.db.entity.ContactItem;
import kz.dasm.baing.db.entity.ContractFinance;
import kz.dasm.baing.db.entity.CurrencyItem;
import kz.dasm.baing.db.entity.ObjectInfo;
import kz.dasm.baing.db.entity.ObjectItem;
import kz.dasm.baing.db.entity.ObjectTreeItem;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author Kreedmart
 */
public class DbApi {

  static private final Logger logger = Logger.getLogger(DbApi.class.getName());
  private DataSource ds;


  public DbApi(DataSource ds) {
    this.ds = ds;
  }


  private Connection getConnection()
          throws SQLException {

    Connection c = ds.getConnection();
    c.setAutoCommit(false);
    return c;
  }


  public Integer initUser(String user, String password)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;
    try {
      conn = getConnection();
      stmt = conn.prepareCall("{? = call PKG_ACCESS.Init_User("
              + "pUser_Name => ?,"
              + "pPassword_Hash => ?)}");

      stmt.registerOutParameter(1, OracleTypes.INTEGER);
      stmt.setString(2, user);
      stmt.setString(3, password);
      stmt.execute();

      Integer userId = (Integer) stmt.getObject(1);

      return userId;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<ObjectTreeItem> getObjectsTree(int userId)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Objects_Tree("
              + "pUser_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, userId);
      stmt.registerOutParameter(2, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(2);

      List<ObjectTreeItem> res = ResultSetMapper.mapToBeanList(
              rs, ObjectTreeItem.class);

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<ObjectItem> getObjectsByType(String typeName)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Objects_By_Type("
              + "pType_Name => ?,"
              + "pCursor => ?)}");

      stmt.setString(1, typeName);
      stmt.registerOutParameter(2, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(2);
      List<ObjectItem> res = ResultSetMapper.mapToBeanList(
              rs, ObjectItem.class);

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<ObjectItem> getClientBrands(int clientId)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Client_Brands("
              + "pClient_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, clientId);
      stmt.registerOutParameter(2, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(2);
      List<ObjectItem> res = ResultSetMapper.mapToBeanList(
              rs, ObjectItem.class);

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<ObjectItem> getBrandActions(int brandId)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Brand_Actions("
              + "pBrand_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, brandId);
      stmt.registerOutParameter(2, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(2);

      List<ObjectItem> res = ResultSetMapper.mapToBeanList(
              rs, ObjectItem.class);

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<ObjectItem> getActionMedia(int brandActionId)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Action_Media("
              + "pBrand_Action_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, brandActionId);
      stmt.registerOutParameter(2, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(2);
      List<ObjectItem> res = ResultSetMapper.mapToBeanList(
              rs, ObjectItem.class);

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<ObjectItem> getContractsByMedia(int userId, int actionMediaId)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Contracts_By_Media("
              + "pUser_ID => ?,"
              + "pAction_Media_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, userId);
      stmt.setInt(2, actionMediaId);
      stmt.registerOutParameter(3, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(3);
      List<ObjectItem> res = ResultSetMapper.mapToBeanList(
              rs, ObjectItem.class);

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public <T extends ObjectInfo> T getObjectInfo(
          int userId, int objectId, Class<T> objInfoCls) throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Object_Info("
              + "pObject_ID => ?,"
              + "pUser_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, objectId);
      stmt.setInt(2, userId);
      stmt.registerOutParameter(3, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(3);
      T res = null;

      if (rs.next()) {
        res = ResultSetMapper.mapToBean(rs, objInfoCls);
      }

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }

  }


  public List<CompanyPerson> getCompanyPersons(int companyId)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Company_Persons("
              + "pCompany_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, companyId);
      stmt.registerOutParameter(2, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(2);
      List<CompanyPerson> res = ResultSetMapper.mapToBeanList(
              rs, CompanyPerson.class);

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<ContactItem> getContacts(int subjectId)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Contacts("
              + "pSubject_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, subjectId);
      stmt.registerOutParameter(2, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(2);
      List<ContactItem> res = ResultSetMapper.mapToBeanList(
              rs, ContactItem.class);

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public String getObjectType(int objectId)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{? = call PKG_OPERATIONS.Get_Object_Type("
              + "pObject_ID => ?)}");

      stmt.registerOutParameter(1, OracleTypes.VARCHAR);
      stmt.setInt(2, objectId);
      stmt.execute();

      String res = stmt.getString(1);

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public void addObjectLink(int masterObjectId, int relatedObjectId)
          throws SQLException {
    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Add_Object_Link("
              + "pMaster_Object_ID => ?,"
              + "pRelated_Object_ID => ?)}");

      stmt.setInt(1, masterObjectId);
      stmt.setInt(2, relatedObjectId);
      stmt.execute();

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public void cutObjectLink(int masterObjectId, int relatedObjectId)
          throws SQLException {
    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Cut_Object_Link("
              + "pMaster_Object_ID => ?,"
              + "pRelated_Object_ID => ?)}");

      stmt.setInt(1, masterObjectId);
      stmt.setInt(2, relatedObjectId);
      stmt.execute();

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public void addContractLink(int providerContractId, int consigneeContractId)
          throws SQLException {
    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Add_Contract_Link("
              + "pProvider_Contract_ID => ?,"
              + "pConsignee_Contract_ID => ?)}");

      stmt.setInt(1, providerContractId);
      stmt.setInt(2, consigneeContractId);
      stmt.execute();

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public void editObject(
          Integer objectId,
          Integer parentObjectId,
          String typeName,
          String objectName,
          String objectShortName,
          String comments)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Edit_Object("
              + "pObject_ID => ?,"
              + "pParent_Object_ID => ?,"
              + "pObject_Type_Name => ?,"
              + "pObject_Name => ?,"
              + "pObject_Short_Name => ?,"
              + "pComments => ?)}");

      if (objectId == null) {
        stmt.setNull(1, OracleTypes.INTEGER);
      } else {
        stmt.setInt(1, objectId);
      }

      if (parentObjectId == null) {
        stmt.setNull(2, OracleTypes.INTEGER);
      } else {
        stmt.setInt(2, parentObjectId);
      }

      if (typeName == null) {
        stmt.setNull(3, OracleTypes.VARCHAR);
      } else {
        stmt.setString(3, typeName);
      }

      stmt.setString(4, objectName);
      stmt.setString(5, objectShortName);
      stmt.setString(6, comments);

      stmt.execute();

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public void editBrandAction(
          Integer brandActionId,
          Integer brandId,
          String name,
          String shortName,
          String comments,
          java.sql.Date startDate,
          java.sql.Date endDate)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Edit_Brand_Action("
              + "pBrand_Action_ID => ?,"
              + "pBrand_ID => ?,"
              + "pName => ?,"
              + "pShort_Name => ?,"
              + "pComments => ?,"
              + "pStart_Date => ?,"
              + "pEnd_Date => ?)}");

      if (brandActionId == null) {
        stmt.setNull(1, OracleTypes.INTEGER);
      } else {
        stmt.setInt(1, brandActionId);
      }

      if (brandId == null) {
        stmt.setNull(2, OracleTypes.INTEGER);
      } else {
        stmt.setInt(2, brandId);
      }

      stmt.setString(3, name);
      stmt.setString(4, shortName);
      stmt.setString(5, comments);
      stmt.setDate(6, startDate);
      stmt.setDate(7, endDate);

      stmt.execute();

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public void editContract(
          Integer contractId,
          Integer providerId,
          Integer consigneeId,
          Integer mediaTypeId,
          String name,
          String shortName,
          String comments,
          Integer currencyId,
          Float summ,
          java.sql.Date signingDate,
          java.sql.Date expireDate,
          Integer responsibleUserId)
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Edit_Contract("
              + "pContract_ID => ?,"
              + "pProvider_ID => ?,"
              + "pConsignee_ID => ?,"
              + "pMedia_Type_ID => ?,"
              + "pName => ?,"
              + "pShort_Name => ?,"
              + "pComments => ?,"
              + "pCurrency_ID => ?,"
              + "pSumm => ?,"
              + "pSigning_Date => ?,"
              + "pExpire_Date => ?,"
              + "pResponsible_User_ID => ?)}");

      stmt.setInt(1, contractId);

      if (providerId == null) {
        stmt.setNull(2, OracleTypes.INTEGER);
      } else {
        stmt.setInt(2, providerId);
      }

      if (consigneeId == null) {
        stmt.setNull(3, OracleTypes.INTEGER);
      } else {
        stmt.setInt(3, consigneeId);
      }

      if (mediaTypeId == null) {
        stmt.setNull(4, OracleTypes.INTEGER);
      } else {
        stmt.setInt(4, mediaTypeId);
      }

      stmt.setString(5, name);
      stmt.setString(6, shortName);
      stmt.setString(7, comments);
      stmt.setInt(8, currencyId);
      stmt.setFloat(9, summ);
      stmt.setDate(10, signingDate);
      stmt.setDate(11, expireDate);
      stmt.setInt(12, responsibleUserId);

      stmt.execute();

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<CurrencyItem> getCurrencies()
          throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Currencies("
              + "pCursor => ?)}");

      stmt.registerOutParameter(1, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(1);

      List<CurrencyItem> res = ResultSetMapper.mapToBeanList(
              rs, CurrencyItem.class);

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<ContractFinance> getContractFinance(
          int userId, int year, int contractId) throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Contract_Finance("
              + "pUser_ID => ?,"
              + "pYear => ?,"
              + "pContract_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, userId);
      stmt.setInt(2, year);
      stmt.setInt(3, contractId);
      stmt.registerOutParameter(4, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(4);

      List<ContractFinance> res = new ArrayList<ContractFinance>();

      while (rs.next()) {
        ContractFinance item = ResultSetMapper.mapToBean(
                rs, ContractFinance.class);

        for (int m = 1; m <= 12; m++) {
          item.getMonthValues().add(rs.getFloat("Month_" + m));
        }

        res.add(item);
      }

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }


  public List<ContractFinance> getContractFinanceByTree(
          int userId, int year, int objectId) throws SQLException {

    Connection conn = null;
    CallableStatement stmt = null;

    try {
      conn = getConnection();
      stmt = conn.prepareCall(
              "{call PKG_OPERATIONS.Get_Contract_Finance_By_Tree("
              + "pUser_ID => ?,"
              + "pYear => ?,"
              + "pObject_ID => ?,"
              + "pCursor => ?)}");

      stmt.setInt(1, userId);
      stmt.setInt(2, year);
      stmt.setInt(3, objectId);
      stmt.registerOutParameter(4, OracleTypes.CURSOR);
      stmt.execute();

      ResultSet rs = (ResultSet) stmt.getObject(4);

      List<ContractFinance> res = new ArrayList<ContractFinance>();

      while (rs.next()) {
        ContractFinance item = ResultSetMapper.mapToBean(
                rs, ContractFinance.class);

        for (int m = 1; m <= 12; m++) {
          item.getMonthValues().add(rs.getFloat("Month_" + m));
        }

        res.add(item);
      }

      rs.close();

      return res;

    } finally {
      JdbcHelper.safeClose(conn, stmt);
    }
  }
}
